package com.scalar.chiptrack.operations.reports.actions;

import com.scalar.chiptrack.ChipTrackExtendedAction;
import com.scalar.chiptrack.operations.process.dao.NewProductDAO;
import com.scalar.chiptrack.operations.process.dao.ProductDao;
import com.scalar.chiptrack.operations.reports.TransactionDetail;
import com.scalar.chiptrack.operations.reports.forms.DetailedTransactionReportForm;
import com.scalar.chiptrack.operations.vendor.dao.VendorDAO;
import com.scalar.chiptrack.utils.DateUtil;
import com.scalar.chiptrack.utils.ExcelUtils;
import com.scalar.chiptrack.utils.ConfigManager;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellRangeAddress;

import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

/**
 * User: Sujan
 * Date: November 11, 2008
 * Time: 02:59:18 PM
 */
public class DetailedTransactionReportAction extends ChipTrackExtendedAction
{

    public ActionForward executeRequest(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response) throws Exception
    {
        String action = request.getParameter( "action" );
        DetailedTransactionReportForm detailedTransactionReportForm = (DetailedTransactionReportForm)form;

        Workbook workbook = null;
        String fileName = "";

        if ( action == null || "".equals(action) )
        {
            action = "detailedTransactionReportSearch";
        }
        try
        {
            if ( "detailedTransactionReportSearch".equalsIgnoreCase(action))
            {
                request.setAttribute ( "baseProductList", NewProductDAO.loadAllBaseProducts( null, true, true ));
                request.setAttribute( "vendorList",    VendorDAO.getVendorNamesByPsType(null, "RMA".equalsIgnoreCase(detailedTransactionReportForm.getProcessType()) ? "FG" : detailedTransactionReportForm.getProcessType() ) );
                if ( detailedTransactionReportForm.isByLot() )
                {
                    request.setAttribute( "productList", NewProductDAO.loadPartNumbers( null, 1, detailedTransactionReportForm.getProcessType(), true, true, true, detailedTransactionReportForm.getBaseProductId() ) );
                }
                return mapping.findForward( "detailedTransactionReportSearch");
            }
            else if ( "generateDetailedTransactionReportXls".equalsIgnoreCase( action ) )
            {
                ArrayList<TransactionDetail> transactionDetailList = null;
                String productName = "";

                if ( detailedTransactionReportForm.isByLot() )
                {
                    //transactionDetailList = NewProductDAO.getDetailedTransactionReportByLotDetails( null, detailedTransactionReportForm.getProcessStepId(), detailedTransactionReportForm.getFromDate(), detailedTransactionReportForm.getToDate(), detailedTransactionReportForm.getProcessType(), detailedTransactionReportForm.getVendorName() );
                    productName = detailedTransactionReportForm.getProcessStepId() > 0 ? ProductDao.getDeviceName( detailedTransactionReportForm.getProcessStepId() ) : "ALL";
                }
                else
                {
                    //transactionDetailList = NewProductDAO.getDetailedTransactionReportDetails( null, detailedTransactionReportForm.getBaseProductId(), detailedTransactionReportForm.getFromDate(), detailedTransactionReportForm.getToDate(), detailedTransactionReportForm.getProcessType(), detailedTransactionReportForm.getVendorName() );
                    productName = detailedTransactionReportForm.getBaseProductId() > 0 ? ProductDao.getBaseProductName( detailedTransactionReportForm.getBaseProductId()+"" ) : "ALL";
                }

                transactionDetailList = NewProductDAO.getDetailedTransactionReportDetails( null, detailedTransactionReportForm );
                workbook = getTransactionWorkBook( transactionDetailList, detailedTransactionReportForm, productName );
                fileName = detailedTransactionReportForm.getProcessType() + " Detail Transaction Report_" + DateUtil.getTimeStamp() ;

                ExcelUtils.generateExcelReport( workbook, fileName, response );
		    }
        }
        catch ( Exception e)
        {
            //errors.put ( "Could not generate excel file", null );
            e.printStackTrace();
        }
        return null;
    }
      
    private Workbook getTransactionWorkBook ( ArrayList<TransactionDetail> transactionDetailList, DetailedTransactionReportForm detailedTransactionReportForm, String baseProductName )
    {
        String title = detailedTransactionReportForm.getProcessType() + " Transaction Report";
        if ( detailedTransactionReportForm.isByLot() )
        {
            title = title + " By Lot";
        }
        Workbook workbook    = null;
        if ( "xlsx".equalsIgnoreCase(ConfigManager.getXLSWorkBookFormat() ) )
        {
            workbook    = new XSSFWorkbook();
        }
        else
        {
            workbook    = new HSSFWorkbook();
        }
        CreationHelper ch = workbook.getCreationHelper();
        DataFormat df = ch.createDataFormat();

        Sheet sheet       = workbook.createSheet( detailedTransactionReportForm.getProcessType() + " Transaction Report" );
        sheet.setSelected( true );

        Font titleFont = workbook.createFont();
        titleFont.setFontHeightInPoints ( ( short ) 12 );
        titleFont.setBoldweight ( Font.BOLDWEIGHT_BOLD );
        titleFont.setFontName( "verdana" );
        titleFont.setColor ( IndexedColors.BLACK.getIndex());

        Font headerFont = workbook.createFont();
        headerFont.setFontHeightInPoints ( ( short ) 10 );
        headerFont.setBoldweight ( Font.BOLDWEIGHT_BOLD );
        headerFont.setFontName( "verdana" );
        headerFont.setColor ( IndexedColors.BLACK.getIndex());

        Font textFont = workbook.createFont();
        textFont.setFontHeightInPoints ( ( short ) 8 );
        textFont.setBoldweight ( Font.BOLDWEIGHT_NORMAL );
        textFont.setFontName( "verdana" );
        textFont.setColor ( IndexedColors.BLACK.getIndex());

        CellStyle titleStyle  = workbook.createCellStyle ( );
        titleStyle.setBorderBottom ( CellStyle.BORDER_THIN );
        titleStyle.setBorderTop ( CellStyle.BORDER_THIN );
        titleStyle.setBorderLeft ( CellStyle.BORDER_THIN );
        titleStyle.setBorderRight ( CellStyle.BORDER_THIN );
        titleStyle.setAlignment ( CellStyle.ALIGN_CENTER );
        titleStyle.setWrapText ( true );
        titleStyle.setFont ( titleFont );

        CellStyle headerStyle  = workbook.createCellStyle ( );
        headerStyle.setBorderBottom ( CellStyle.BORDER_THIN );
        headerStyle.setBorderTop ( CellStyle.BORDER_THIN );
        headerStyle.setBorderLeft ( CellStyle.BORDER_THIN );
        headerStyle.setBorderRight ( CellStyle.BORDER_THIN );
        headerStyle.setAlignment ( CellStyle.ALIGN_CENTER );
        headerStyle.setWrapText ( true );
        headerStyle.setFont ( headerFont );

        CellStyle textStyle  = workbook.createCellStyle ( );
        textStyle.setBorderBottom ( CellStyle.BORDER_THIN );
        textStyle.setBorderTop ( CellStyle.BORDER_THIN );
        textStyle.setBorderLeft ( CellStyle.BORDER_THIN );
        textStyle.setBorderRight ( CellStyle.BORDER_THIN );
        textStyle.setAlignment ( CellStyle.ALIGN_LEFT );
        textStyle.setWrapText ( true );
        textStyle.setFont ( textFont );

        CellStyle textValueStyle  = workbook.createCellStyle ( );
        textValueStyle.setBorderBottom ( CellStyle.BORDER_THIN );
        textValueStyle.setBorderTop ( CellStyle.BORDER_THIN );
        textValueStyle.setBorderLeft ( CellStyle.BORDER_THIN );
        textValueStyle.setBorderRight ( CellStyle.BORDER_THIN );
        textValueStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
        textValueStyle.setWrapText ( true );
        textValueStyle.setFont ( textFont );
        textValueStyle.setDataFormat( df.getFormat( "#,##0" ) );
        
        CellStyle floatValueStyle  = workbook.createCellStyle ( );
        floatValueStyle.setBorderBottom ( CellStyle.BORDER_THIN );
        floatValueStyle.setBorderTop ( CellStyle.BORDER_THIN );
        floatValueStyle.setBorderLeft ( CellStyle.BORDER_THIN );
        floatValueStyle.setBorderRight ( CellStyle.BORDER_THIN );
        floatValueStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
        floatValueStyle.setWrapText ( true );
        floatValueStyle.setFont ( textFont );
        floatValueStyle.setDataFormat( df.getFormat( "0.0000" ) );

        CellStyle headerValueStyle  = workbook.createCellStyle ( );
        headerValueStyle.setBorderBottom ( CellStyle.BORDER_THIN );
        headerValueStyle.setBorderTop ( CellStyle.BORDER_THIN );
        headerValueStyle.setBorderLeft ( CellStyle.BORDER_THIN );
        headerValueStyle.setBorderRight ( CellStyle.BORDER_THIN );
        headerValueStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
        headerValueStyle.setWrapText ( true );
        headerValueStyle.setFont ( headerFont );
        headerValueStyle.setDataFormat( df.getFormat( "#,##0" ) );

        int nextRowNumber = 0;
        String partNumberLabel = "Part Number";
        String lotNumberLabel = "Lot Number";
        String startingQtyLabel = "Starting Qty";
        String inQtyLabel = "In Qty";
        String outQtyLabel = "Out Qty";
        String inFromFGLabel = "In from FG";
        String inFromTRLabel = "In from TR";
        String inFromRMALabel = "In from RMA";
        String inFromOtherLabel = "In from Other";
        String shipmentsToCustomerLabel = "Shipments To Customer";
        String scrapQtyLabel = "Scrap Qty";
        String endingQtyLabel = "Ending Qty";
        String yieldLossLabel = "Yield Loss";
        String availableQtyLabel = "Available Qty";
        String actualInQtyLabel = "Actual In Qty";  //without scrap i.e only yield loss is present
        String actualOutQtyLabel = "Actual Out Qty";  //without yield loss
        String diebankPartNumber = "Die Bank Part Number";
        String transferToBulk = "Transfer to Bulk";
        /*
        String startingQtyValueLabel = "Starting Qty ";
        String inQtyValueLabel = "In Qty ";
        String outQtyValueLabel = "Out Qty ";
        String scrapQtyValueLabel = "Scrap Qty ";
        String endingQtyValueLabel = "Ending Qty ";
        String adjustmentValueLabel = "Adjustment";
        String unitStdCostInLabel = "Unit Std Cost In";
        String inFromTRValueLabel = "In from TR";
        String inFromRMAValueLabel = "In From RMA ";
        String inFromOtherValueLabel = "In from Other ";
        String availableQtyValueLabel = "Available Qty ";
        String shipmentsToCustomerValueLabel = "Shipments To Customer ";*/
        String outQtyTRLabel = "Transfer to TR Qty";
        String outQtyFTLabel = "Transfer to Test Qty";
        String outQtyFGLabel = "Transfer to FG Qty";
        /*String outQtyTRValueLabel = "Transfer to TR Qty ";
        String outQtyFTValueLabel = "Transfer to Test Qty ";
        String outQtyFGValueLabel = "Transfer to FG Qty ";
        String emptyCol = "";
        String emptyCol1 = " ";
        String emptyCol2 = "  ";
        String stdOutUnits = "Std Units Out";
        String unitsYieldVariance = "Units Yield Variance";
        String actualYield = "Actual Yield";
        String stdValueIntoOperation = "Std Value into Operation";
        String stdValueAddEprom = "Std Value add eProm";
        String stdValueAddAtTest = "Std Value add Test";
        String stdValueAddAtTR = "Std Value add TR";
        String stdValueAddBG = "Std Value add BG";
        String stdValueAddDieFreight = "Std Value add Die Freight";
        String stdValueAddAssembly = "Std Value add Assembly";
        String stdValueAddAssyFreight = "Std Value add Assembly Freight";
        String totalStdValueAdd = "Total Std Value Add";
        String stdValueOutOperation = "Std value out of Operation";
        String yieldVariance = "Yield Variance";
        String unitStdCostInDieLabel = "Unit Std cost in Die "; 
        String stdValueAddEpromLabel = "Std value add eProm ";
        String stdValueAddBGLabel = "Std value add BG ";
        String stdValueAddDieFrieghtLabel = "Std value add Die Freight ";
        String stdValueAddAssyLabel = "Std Value add Assembly ";
        String stdValueAddAssyFreightLabel = "Std value add Assembly Freight ";
        String stdUnitCostOutLabel = "Unit Std Cost out";
        String stdUnitValueAddLabel = "Unit Std value added";
        String stdYieldLabel = "Std Yield At Assembly";
        String inFromFGValueLabel = "In from FG ";*/
        String headers[] = null;
        
        if ( "Die Bank".equalsIgnoreCase( detailedTransactionReportForm.getProcessType() ) )
        {            
            startingQtyLabel = "Beginning Inventory";
            inQtyLabel = "Wafer Receipts";
            outQtyLabel = "Transferred to Assembly";
            yieldLossLabel = "Delta";
            //startingQtyValueLabel = "Beginning Inventory ";
            //inQtyValueLabel = "Wafer Receipts ";
            //outQtyValueLabel = "Transferred to Assembly ";
            
            if( detailedTransactionReportForm.isByLot() )
            {
            	//headers = new String[]{partNumberLabel, lotNumberLabel, startingQtyLabel, inQtyLabel, outQtyLabel, scrapQtyLabel, endingQtyLabel, yieldLossLabel, emptyCol, startingQtyValueLabel, inQtyValueLabel, outQtyValueLabel, scrapQtyValueLabel, endingQtyValueLabel, adjustmentValueLabel, emptyCol1, unitStdCostInLabel };
            	headers = new String[]{partNumberLabel, lotNumberLabel, startingQtyLabel, inQtyLabel, outQtyLabel, scrapQtyLabel, endingQtyLabel, yieldLossLabel };
            }
            else
            {
            	//headers = new String[]{partNumberLabel, startingQtyLabel, inQtyLabel, outQtyLabel, scrapQtyLabel, endingQtyLabel, yieldLossLabel, emptyCol, startingQtyValueLabel, inQtyValueLabel, outQtyValueLabel, scrapQtyValueLabel, endingQtyValueLabel, adjustmentValueLabel, emptyCol1, unitStdCostInLabel };
            	headers = new String[]{partNumberLabel, startingQtyLabel, inQtyLabel, outQtyLabel, scrapQtyLabel, endingQtyLabel, yieldLossLabel };
            }
        }
        else if ( "Assembly".equalsIgnoreCase( detailedTransactionReportForm.getProcessType() ) )
        {
            inQtyLabel = "In From DB";
            //inQtyValueLabel = "In From DB ";
            outQtyLabel = "Assembly Out Qty";
            actualInQtyLabel = "Assembly In Qty";
            if( detailedTransactionReportForm.isByLot() )
            {
            	headers = new String[]{partNumberLabel, lotNumberLabel, diebankPartNumber, startingQtyLabel, inQtyLabel, availableQtyLabel, scrapQtyLabel, endingQtyLabel, actualInQtyLabel, outQtyLabel, yieldLossLabel };
            	//headers = new String[]{partNumberLabel, lotNumberLabel, diebankPartNumber, startingQtyLabel, inQtyLabel, availableQtyLabel, scrapQtyLabel, endingQtyLabel, actualInQtyLabel, outQtyLabel, yieldLossLabel, emptyCol, stdOutUnits, unitsYieldVariance, actualYield, emptyCol1, startingQtyValueLabel, inQtyValueLabel, availableQtyValueLabel, scrapQtyValueLabel, endingQtyValueLabel, stdValueIntoOperation, stdValueAddEprom, stdValueAddBG, stdValueAddDieFreight, stdValueAddAssembly, stdValueAddAssyFreight, totalStdValueAdd, stdValueOutOperation, yieldVariance, emptyCol2, unitStdCostInDieLabel, stdValueAddEpromLabel, stdValueAddBGLabel, stdValueAddDieFrieghtLabel, stdValueAddAssyLabel, stdValueAddAssyFreightLabel, stdUnitCostOutLabel, stdYieldLabel };
            }
            else
            {
            	//headers = new String[]{partNumberLabel, diebankPartNumber, startingQtyLabel, inQtyLabel, availableQtyLabel, scrapQtyLabel, endingQtyLabel, actualInQtyLabel, outQtyLabel, yieldLossLabel, emptyCol, stdOutUnits, unitsYieldVariance, actualYield, emptyCol1, startingQtyValueLabel, inQtyValueLabel, availableQtyValueLabel, scrapQtyValueLabel, endingQtyValueLabel, stdValueIntoOperation, stdValueAddEprom, stdValueAddBG, stdValueAddDieFreight, stdValueAddAssembly, stdValueAddAssyFreight, totalStdValueAdd, stdValueOutOperation, yieldVariance, emptyCol2, unitStdCostInDieLabel, stdValueAddEpromLabel, stdValueAddBGLabel, stdValueAddDieFrieghtLabel, stdValueAddAssyLabel, stdValueAddAssyFreightLabel, stdUnitCostOutLabel, stdYieldLabel };
            	headers = new String[]{partNumberLabel, diebankPartNumber, startingQtyLabel, inQtyLabel, availableQtyLabel, scrapQtyLabel, endingQtyLabel, actualInQtyLabel, outQtyLabel, yieldLossLabel };
            }
        }
        else if ( "FT".equalsIgnoreCase( detailedTransactionReportForm.getProcessType() ) )
        {
            inQtyLabel = "In From Assy";
            outQtyLabel = "Test Out Qty";
            actualInQtyLabel = "Test In Qty";
            //inQtyValueLabel = "In From Assy ";
            
            if( detailedTransactionReportForm.isByLot() )
            {
            	//headers = new String[]{partNumberLabel, lotNumberLabel, startingQtyLabel, inQtyLabel, inFromRMALabel, inFromOtherLabel, availableQtyLabel, scrapQtyLabel, endingQtyLabel, actualInQtyLabel, outQtyLabel, yieldLossLabel, emptyCol, stdOutUnits, unitsYieldVariance, actualYield, emptyCol1, startingQtyValueLabel, inQtyValueLabel, inFromRMAValueLabel, inFromOtherValueLabel, availableQtyValueLabel, scrapQtyValueLabel, endingQtyValueLabel, stdValueIntoOperation, stdValueAddAtTest, stdValueOutOperation, yieldVariance, emptyCol2, unitStdCostInLabel, stdUnitValueAddLabel, stdUnitCostOutLabel, stdYieldLabel };
            	headers = new String[]{partNumberLabel, lotNumberLabel, startingQtyLabel, inQtyLabel, inFromRMALabel, inFromOtherLabel, availableQtyLabel, scrapQtyLabel, endingQtyLabel, actualInQtyLabel, outQtyLabel, yieldLossLabel };
            }
            else
            {
            	//headers = new String[]{partNumberLabel, startingQtyLabel, inQtyLabel, inFromRMALabel, inFromOtherLabel, availableQtyLabel, scrapQtyLabel, endingQtyLabel, actualInQtyLabel, outQtyLabel, yieldLossLabel, emptyCol, stdOutUnits, unitsYieldVariance, actualYield, emptyCol1, startingQtyValueLabel, inQtyValueLabel, inFromRMAValueLabel, inFromOtherValueLabel, availableQtyValueLabel, scrapQtyValueLabel, endingQtyValueLabel, stdValueIntoOperation, stdValueAddAtTest, stdValueOutOperation, yieldVariance, emptyCol2, unitStdCostInLabel, stdUnitValueAddLabel, stdUnitCostOutLabel, stdYieldLabel };
            	headers = new String[]{partNumberLabel, startingQtyLabel, inQtyLabel, inFromRMALabel, inFromOtherLabel, availableQtyLabel, scrapQtyLabel, endingQtyLabel, actualInQtyLabel, outQtyLabel, yieldLossLabel };
            }
        }
        else if ( "Tape & Reel".equalsIgnoreCase( detailedTransactionReportForm.getProcessType() ) )
        {
            inQtyLabel = "In From Test";
            outQtyLabel = "TR Out Qty";
            actualInQtyLabel = "TR In Qty";
            //inQtyValueLabel = "In From Test";

            if( detailedTransactionReportForm.isByLot() )
            {
            	//headers = new String[]{partNumberLabel, lotNumberLabel, startingQtyLabel, inQtyLabel, inFromFGLabel, inFromRMALabel, inFromOtherLabel, availableQtyLabel, scrapQtyLabel, endingQtyLabel, actualInQtyLabel, outQtyLabel, yieldLossLabel, emptyCol, stdOutUnits, unitsYieldVariance, actualYield, emptyCol1, startingQtyValueLabel, inQtyValueLabel, inFromFGValueLabel, inFromRMAValueLabel, inFromOtherValueLabel, availableQtyValueLabel, scrapQtyValueLabel, endingQtyValueLabel, stdValueIntoOperation, stdValueAddAtTR, stdValueOutOperation, yieldVariance, emptyCol2, unitStdCostInLabel, stdUnitValueAddLabel, stdUnitCostOutLabel, stdYieldLabel  };
            	headers = new String[]{partNumberLabel, lotNumberLabel, startingQtyLabel, inQtyLabel, inFromFGLabel, inFromRMALabel, inFromOtherLabel, availableQtyLabel, scrapQtyLabel, endingQtyLabel, transferToBulk, actualInQtyLabel, outQtyLabel, yieldLossLabel };
            }
            else
            {
            	//headers = new String[]{partNumberLabel, startingQtyLabel, inQtyLabel, inFromFGLabel, inFromRMALabel, inFromOtherLabel, availableQtyLabel, scrapQtyLabel, endingQtyLabel, actualInQtyLabel, outQtyLabel, yieldLossLabel, emptyCol, stdOutUnits, unitsYieldVariance, actualYield, emptyCol1, startingQtyValueLabel, inQtyValueLabel, inFromFGValueLabel, inFromRMAValueLabel, inFromOtherValueLabel, availableQtyValueLabel, scrapQtyValueLabel, endingQtyValueLabel, stdValueIntoOperation, stdValueAddAtTR, stdValueOutOperation, yieldVariance, emptyCol2, unitStdCostInLabel, stdUnitValueAddLabel, stdUnitCostOutLabel, stdYieldLabel  };
            	headers = new String[]{partNumberLabel, startingQtyLabel, inQtyLabel, inFromFGLabel, inFromRMALabel, inFromOtherLabel, availableQtyLabel, scrapQtyLabel, endingQtyLabel, transferToBulk, actualInQtyLabel, outQtyLabel, yieldLossLabel };
            }
        }
        else if ("RMA".equalsIgnoreCase( detailedTransactionReportForm.getProcessType() ) )
        {
            inQtyLabel = "In From Test";

            actualInQtyLabel = "FG In Qty";
            actualOutQtyLabel = "FG Out Qty";
            shipmentsToCustomerLabel = "Customer Returns received";
            //shipmentsToCustomerValueLabel = "Customer Returns received ";
            if( detailedTransactionReportForm.isByLot() )
            {
            	headers = new String[]{partNumberLabel, lotNumberLabel, startingQtyLabel, shipmentsToCustomerLabel, inFromOtherLabel, outQtyFTLabel, outQtyTRLabel, outQtyFGLabel, scrapQtyLabel, endingQtyLabel };
            	//headers = new String[]{partNumberLabel, lotNumberLabel, startingQtyLabel, shipmentsToCustomerLabel, inFromOtherLabel, outQtyFTLabel, outQtyTRLabel, outQtyFGLabel, scrapQtyLabel, endingQtyLabel, emptyCol,startingQtyValueLabel, shipmentsToCustomerValueLabel, inFromOtherValueLabel, outQtyFTValueLabel, outQtyTRValueLabel, outQtyFGValueLabel, scrapQtyValueLabel, endingQtyValueLabel, emptyCol1, unitStdCostInLabel };
            }
            else
            {
            	headers = new String[]{partNumberLabel, startingQtyLabel, shipmentsToCustomerLabel, inFromOtherLabel,  outQtyFTLabel, outQtyTRLabel, outQtyFGLabel, scrapQtyLabel, endingQtyLabel };
            	//headers = new String[]{partNumberLabel, startingQtyLabel, shipmentsToCustomerLabel, inFromOtherLabel,  outQtyFTLabel, outQtyTRLabel, outQtyFGLabel, scrapQtyLabel, endingQtyLabel, emptyCol,startingQtyValueLabel, shipmentsToCustomerValueLabel, inFromOtherValueLabel, outQtyFTValueLabel, outQtyTRValueLabel, outQtyFGValueLabel, scrapQtyValueLabel, endingQtyValueLabel, emptyCol1, unitStdCostInLabel};
            }

        }
        else//FG
        {
            inQtyLabel = "In From Test";
            outQtyLabel = "Transfer to TR Qty";
            actualInQtyLabel = "FG In Qty";
            actualOutQtyLabel = "FG Out Qty";
            //inQtyValueLabel = "In From Test ";
            //outQtyValueLabel = "Transfer to TR Qty ";
            
            if( detailedTransactionReportForm.isByLot() )
            {
            	//headers = new String[]{partNumberLabel, lotNumberLabel, startingQtyLabel, inQtyLabel, inFromTRLabel, inFromRMALabel, inFromOtherLabel, availableQtyLabel, outQtyLabel, shipmentsToCustomerLabel, scrapQtyLabel, endingQtyLabel, actualInQtyLabel, actualOutQtyLabel, yieldLossLabel, emptyCol, startingQtyValueLabel, inQtyValueLabel, inFromTRValueLabel, inFromRMAValueLabel, inFromOtherValueLabel, availableQtyValueLabel, outQtyValueLabel, shipmentsToCustomerValueLabel, scrapQtyValueLabel, endingQtyValueLabel, adjustmentValueLabel, emptyCol1, unitStdCostInLabel };
            	headers = new String[]{partNumberLabel, lotNumberLabel, startingQtyLabel, inQtyLabel, inFromTRLabel, inFromRMALabel, inFromOtherLabel, availableQtyLabel, outQtyLabel, shipmentsToCustomerLabel, scrapQtyLabel, endingQtyLabel, actualInQtyLabel, actualOutQtyLabel, yieldLossLabel };
            }
            else
            {
            	//headers = new String[]{partNumberLabel, startingQtyLabel, inQtyLabel, inFromTRLabel, inFromRMALabel, inFromOtherLabel, availableQtyLabel, outQtyLabel, shipmentsToCustomerLabel, scrapQtyLabel, endingQtyLabel, actualInQtyLabel, actualOutQtyLabel, yieldLossLabel, emptyCol, startingQtyValueLabel, inQtyValueLabel, inFromTRValueLabel, inFromRMAValueLabel, inFromOtherValueLabel, availableQtyValueLabel, outQtyValueLabel, shipmentsToCustomerValueLabel, scrapQtyValueLabel, endingQtyValueLabel, adjustmentValueLabel, emptyCol1, unitStdCostInLabel};
            	headers = new String[]{partNumberLabel, startingQtyLabel, inQtyLabel, inFromTRLabel, inFromRMALabel, inFromOtherLabel, availableQtyLabel, outQtyLabel, shipmentsToCustomerLabel, scrapQtyLabel, endingQtyLabel, actualInQtyLabel, actualOutQtyLabel, yieldLossLabel };
            }
            
        }

        List<String> columnList = Arrays.asList( headers );

        Row row = sheet.createRow( nextRowNumber++ );
        Cell cell = mergeCells ( sheet, 0, (short)0, 0, (short)(headers.length-1), titleStyle );
        setCellValue ( cell, ch.createRichTextString(title), titleStyle );

        int nextColumnIdx = 0;
        row = sheet.createRow ( nextRowNumber++ ); //empty row

        if ( detailedTransactionReportForm.isByLot() )
        {
            setCellValue ( row, (short)nextColumnIdx++, ch.createRichTextString("Part Number :"), headerStyle );
        }
        else
        {
            setCellValue ( row, (short)nextColumnIdx++, ch.createRichTextString("Base Product :"), headerStyle );
        }
        setCellValue( row, (short)nextColumnIdx++, ch.createRichTextString(baseProductName), textStyle  );
        setCellValue ( row, (short)nextColumnIdx++, ch.createRichTextString("From date :"), headerStyle );
        setCellValue( row, (short)nextColumnIdx++, ch.createRichTextString(detailedTransactionReportForm.getFromDate() ), textStyle  );
        setCellValue ( row, (short)nextColumnIdx++, ch.createRichTextString("To date :"), headerStyle );
        setCellValue( row, (short)nextColumnIdx++, ch.createRichTextString(detailedTransactionReportForm.getToDate() ), textStyle  );
        setCellValue ( row, (short)nextColumnIdx++,ch.createRichTextString( " Vendor : "), headerStyle );
        setCellValue( row, (short)nextColumnIdx++, "0".equalsIgnoreCase(detailedTransactionReportForm.getVendorName()) ? ch.createRichTextString("ALL") : ch.createRichTextString(detailedTransactionReportForm.getVendorName()), textStyle  );

        row = sheet.createRow ( nextRowNumber++ ); //empty row

        row = sheet.createRow ( nextRowNumber++ );
        for ( int i = 0; i < headers.length; i++ )
        {
            cell    = row.createCell( i );
            setCellValue ( cell, ch.createRichTextString(headers[i]), headerStyle );
            int width = ( headers[i].length() > 12 ) ? headers[i].length() : 12;
            sheet.setColumnWidth( i,  ( ( ( width + 2 ) * 256 ) ) );
        }
        nextColumnIdx = 0;
        for ( int i = 0; i < transactionDetailList.size() ; i++ )
        {
            TransactionDetail transactionDetail = (TransactionDetail)transactionDetailList.get(i);
            row = sheet.createRow( nextRowNumber++ );
            
            setCellValue( row, (short)nextColumnIdx++, ch.createRichTextString(transactionDetail.getPartNumber()), textStyle  );
            if ( detailedTransactionReportForm.isByLot() )
            {
                setCellValue( row, (short)nextColumnIdx++, ch.createRichTextString(transactionDetail.getLotNumber()), textStyle  );
            }

            if ( "Assembly".equalsIgnoreCase( detailedTransactionReportForm.getProcessType() ) )
            {
                setCellValue( row, (short)nextColumnIdx++, ch.createRichTextString(transactionDetail.getDieBankPartNumber()), textStyle  );
            }
            setCellValue( row, nextColumnIdx++, transactionDetail.getStartingQty(), textValueStyle, Cell.CELL_TYPE_NUMERIC  );
            
            
            if ( "Die Bank".equalsIgnoreCase( detailedTransactionReportForm.getProcessType() ) )
            {
            	setCellValue( row, nextColumnIdx++, transactionDetail.getInQty(), textValueStyle, Cell.CELL_TYPE_NUMERIC  );
                /*String availabelQtyFormula = getCellReference(columnList.indexOf(startingQtyLabel), nextRowNumber) + "+"
                                + getCellReference(columnList.indexOf(inQtyLabel), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, availabelQtyFormula, textValueStyle );*/
                setCellValue( row, nextColumnIdx++, transactionDetail.getOutQty(), textValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, nextColumnIdx++, transactionDetail.getScrapQty(), textValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, nextColumnIdx++, transactionDetail.getEndingQty(), textValueStyle, Cell.CELL_TYPE_NUMERIC  );
                String yieldLossFormula = getCellReference(columnList.indexOf(startingQtyLabel), nextRowNumber) +
                                    "+" + getCellReference(columnList.indexOf(inQtyLabel), nextRowNumber) +
                                    "-" + getCellReference(columnList.indexOf(outQtyLabel), nextRowNumber) +
                                    "-" + getCellReference(columnList.indexOf(scrapQtyLabel), nextRowNumber) +
                                    "-" + getCellReference(columnList.indexOf(endingQtyLabel), nextRowNumber);

                setCellFormula( row, (short)nextColumnIdx++, yieldLossFormula, textValueStyle );
                /*
                nextColumnIdx++; // Empty Row
                
                String formula = getCellReference(columnList.indexOf(startingQtyLabel), nextRowNumber) + 
                				"*" + getCellReference(columnList.indexOf(unitStdCostInLabel), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, formula, textValueStyle );
                
                formula = getCellReference(columnList.indexOf(inQtyLabel), nextRowNumber) + 
				"*" + getCellReference(columnList.indexOf(unitStdCostInLabel), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, formula, textValueStyle );
                
                formula = getCellReference(columnList.indexOf(outQtyLabel), nextRowNumber) + 
				"*" + getCellReference(columnList.indexOf(unitStdCostInLabel), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, formula, textValueStyle );
                
                formula = getCellReference(columnList.indexOf(scrapQtyLabel), nextRowNumber) + 
				"*" + getCellReference(columnList.indexOf(unitStdCostInLabel), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, formula, textValueStyle );
                
                formula = getCellReference(columnList.indexOf(endingQtyLabel), nextRowNumber) + 
				"*" + getCellReference(columnList.indexOf(unitStdCostInLabel), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, formula, textValueStyle );
                
                formula = getCellReference(columnList.indexOf(yieldLossLabel), nextRowNumber) + 
				"*" + getCellReference(columnList.indexOf(unitStdCostInLabel), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, formula, textValueStyle );
                nextColumnIdx++; // Empty Row
                setCellValue( row, (short)nextColumnIdx++, transactionDetail.getUnitStdCostIn(), floatValueStyle, Cell.CELL_TYPE_NUMERIC  );
                */
            }
            else if ( "Assembly".equalsIgnoreCase( detailedTransactionReportForm.getProcessType() ) )
            {
            	setCellValue( row, nextColumnIdx++, transactionDetail.getInQty(), textValueStyle, Cell.CELL_TYPE_NUMERIC  );
                String availabelQtyFormula = getCellReference(columnList.indexOf(startingQtyLabel), nextRowNumber) + "+"
                                + getCellReference(columnList.indexOf(inQtyLabel), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, availabelQtyFormula, textValueStyle );                
                setCellValue( row, nextColumnIdx++, transactionDetail.getScrapQty(), textValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, nextColumnIdx++, transactionDetail.getEndingQty(), textValueStyle, Cell.CELL_TYPE_NUMERIC  );
                String actualQtyInFormula = getCellReference(columnList.indexOf(availableQtyLabel), nextRowNumber) +
                                    "-" + getCellReference(columnList.indexOf(scrapQtyLabel), nextRowNumber) +
                                    "-" + getCellReference(columnList.indexOf(endingQtyLabel), nextRowNumber) ;
                setCellFormula( row, (short)nextColumnIdx++, actualQtyInFormula, textValueStyle );
                setCellValue( row, nextColumnIdx++, transactionDetail.getOutQty(), textValueStyle, Cell.CELL_TYPE_NUMERIC  );
                String yieldLossFormula = getCellReference(columnList.indexOf(actualInQtyLabel), nextRowNumber) +
                                    "-" + getCellReference(columnList.indexOf(outQtyLabel), nextRowNumber) ;
                setCellFormula( row, (short)nextColumnIdx++, yieldLossFormula, textValueStyle );
                /*
                nextColumnIdx++;
                //  stdOutUnits
                String formula = getCellReference(columnList.indexOf(actualInQtyLabel), nextRowNumber)+ 
                "*"+getCellReference(columnList.indexOf(stdYieldLabel), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, formula, textValueStyle );
                // Units Yield Variance
                formula = getCellReference(columnList.indexOf(outQtyLabel), nextRowNumber)+ 
				"-"+getCellReference(columnList.indexOf(stdOutUnits), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, formula, textValueStyle );       
                // Actaul Yield
                formula = "IF(" + getCellReference(columnList.indexOf(actualInQtyLabel), nextRowNumber)+ "=0, 0,"
				+getCellReference(columnList.indexOf(outQtyLabel), nextRowNumber)
				+ "/" + getCellReference(columnList.indexOf(actualInQtyLabel), nextRowNumber)+ ")";
                setCellFormula( row, (short)nextColumnIdx++, formula, floatValueStyle );       
                
                nextColumnIdx++;
                
                // Starting Qty Value
                formula = getCellReference(columnList.indexOf(startingQtyLabel), nextRowNumber)+ 
				"*"+getCellReference(columnList.indexOf(unitStdCostInDieLabel), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, formula, textValueStyle );       
                
                // In From DB Value
                formula = getCellReference(columnList.indexOf(inQtyLabel), nextRowNumber)+ 
				"*"+getCellReference(columnList.indexOf(unitStdCostInDieLabel), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, formula, textValueStyle );      
                
                // Available Qty Value
                formula = getCellReference(columnList.indexOf(availableQtyLabel), nextRowNumber)+ 
				"*"+getCellReference(columnList.indexOf(unitStdCostInDieLabel), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, formula, textValueStyle );                   

                // Scrap Qty Value
                formula = getCellReference(columnList.indexOf(scrapQtyLabel), nextRowNumber)+ 
				"*"+getCellReference(columnList.indexOf(unitStdCostInDieLabel), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, formula, textValueStyle );                   

                // Ending Qty Value
                formula = getCellReference(columnList.indexOf(endingQtyLabel), nextRowNumber)+ 
				"*"+getCellReference(columnList.indexOf(unitStdCostInDieLabel), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, formula, textValueStyle );                   

                // Std Value into Operation
                formula = getCellReference(columnList.indexOf(actualInQtyLabel), nextRowNumber)+ 
				"*"+getCellReference(columnList.indexOf(unitStdCostInDieLabel), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, formula, floatValueStyle );                   

                // Std Value Add EProm
                formula = getCellReference(columnList.indexOf(actualInQtyLabel), nextRowNumber)+ 
				"*"+getCellReference(columnList.indexOf(stdValueAddEpromLabel), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, formula, floatValueStyle );   

                // Std Value Add BG 
                formula = getCellReference(columnList.indexOf(actualInQtyLabel), nextRowNumber)+ 
				"*"+getCellReference(columnList.indexOf(stdValueAddBGLabel), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, formula, floatValueStyle );  

                // Std Value Add Die Freight
                formula = getCellReference(columnList.indexOf(actualInQtyLabel), nextRowNumber)+ 
				"*"+getCellReference(columnList.indexOf(stdValueAddDieFrieghtLabel), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, formula, floatValueStyle );  

                // Std Value Add Assembly
                formula = getCellReference(columnList.indexOf(actualInQtyLabel), nextRowNumber)+ 
				"*"+getCellReference(columnList.indexOf(stdValueAddAssyLabel), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, formula, floatValueStyle );  

                // Std Value Add Assembly Freight
                formula = getCellReference(columnList.indexOf(actualInQtyLabel), nextRowNumber)+ 
				"*"+getCellReference(columnList.indexOf(stdValueAddAssyFreightLabel), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, formula, floatValueStyle );  
                
                // Total Std Value Add
                formula = "SUM(" + getCellReference(columnList.indexOf(stdValueAddEprom), nextRowNumber)+ 
				":"+getCellReference(columnList.indexOf(stdValueAddAssyFreight), nextRowNumber) + ")";
                setCellFormula( row, (short)nextColumnIdx++, formula, floatValueStyle );  
                
                // Std Value Out Of Operation
                formula = getCellReference(columnList.indexOf(outQtyLabel), nextRowNumber)+ 
				"*"+getCellReference(columnList.indexOf(stdUnitCostOutLabel), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, formula, floatValueStyle );  
                
                // Yield Variance
                formula = getCellReference(columnList.indexOf(stdValueOutOperation), nextRowNumber)+ 
                "-"+getCellReference(columnList.indexOf(totalStdValueAdd), nextRowNumber)+
				"-"+getCellReference(columnList.indexOf(stdValueIntoOperation), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, formula, floatValueStyle ); 
                
                nextColumnIdx++;
                
                setCellValue( row, (short)nextColumnIdx++, transactionDetail.getUnitStdCostIn(), floatValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, (short)nextColumnIdx++, transactionDetail.getUnitStdValueAddEprom(), floatValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, (short)nextColumnIdx++, transactionDetail.getUnitStdValueAddBG(), floatValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, (short)nextColumnIdx++, transactionDetail.getUnitStdValueDieFrieght(), floatValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, (short)nextColumnIdx++, transactionDetail.getUnitStdValueAdd(), floatValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, (short)nextColumnIdx++, transactionDetail.getUnitStdValueAssyFrieght(), floatValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, (short)nextColumnIdx++, transactionDetail.getUnitStdCostOut(), floatValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, (short)nextColumnIdx++, transactionDetail.getStdYield(), floatValueStyle, Cell.CELL_TYPE_NUMERIC  );
                */
            }
            else if ( "FT".equalsIgnoreCase( detailedTransactionReportForm.getProcessType() ) )
            {
            	setCellValue( row, nextColumnIdx++, transactionDetail.getInQty(), textValueStyle, Cell.CELL_TYPE_NUMERIC  );
            	setCellValue( row, nextColumnIdx++, transactionDetail.getInFromRMA(), textValueStyle, Cell.CELL_TYPE_NUMERIC  );
            	setCellValue( row, nextColumnIdx++, transactionDetail.getInFromOther(), textValueStyle, Cell.CELL_TYPE_NUMERIC  );
                String availabelQtyFormula = getCellReference(columnList.indexOf(startingQtyLabel), nextRowNumber) + "+"
                                + getCellReference(columnList.indexOf(inQtyLabel), nextRowNumber)+ "+" + getCellReference(columnList.indexOf(inFromRMALabel), nextRowNumber)
                                + "+" + getCellReference(columnList.indexOf(inFromOtherLabel), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, availabelQtyFormula, textValueStyle );
                setCellValue( row, nextColumnIdx++, transactionDetail.getScrapQty(), textValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, nextColumnIdx++, transactionDetail.getEndingQty(), textValueStyle, Cell.CELL_TYPE_NUMERIC  );
                String actualQtyInFormula = getCellReference(columnList.indexOf(availableQtyLabel), nextRowNumber) +
                                    "-" + getCellReference(columnList.indexOf(scrapQtyLabel), nextRowNumber) +
                                    "-" + getCellReference(columnList.indexOf(endingQtyLabel), nextRowNumber) ;
                setCellFormula( row, (short)nextColumnIdx++, actualQtyInFormula, textValueStyle );
                setCellValue( row, nextColumnIdx++, transactionDetail.getOutQty(), textValueStyle, Cell.CELL_TYPE_NUMERIC  );
                String yieldLossFormula = getCellReference(columnList.indexOf(actualInQtyLabel), nextRowNumber) +
                                    "-" + getCellReference(columnList.indexOf(outQtyLabel), nextRowNumber) ;
                setCellFormula( row, (short)nextColumnIdx++, yieldLossFormula, textValueStyle );
                /*
                nextColumnIdx++;
                //  stdOutUnits
                String formula = getCellReference(columnList.indexOf(actualInQtyLabel), nextRowNumber)+ 
                "*"+getCellReference(columnList.indexOf(stdYieldLabel), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, formula, textValueStyle );
                // Units Yield Variance
                formula = getCellReference(columnList.indexOf(outQtyLabel), nextRowNumber)+ 
				"-"+getCellReference(columnList.indexOf(stdOutUnits), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, formula, textValueStyle );       
                // Actaul Yield
                formula = "IF(" + getCellReference(columnList.indexOf(actualInQtyLabel), nextRowNumber)+ "=0, 0,"
				+getCellReference(columnList.indexOf(outQtyLabel), nextRowNumber)
				+ "/" + getCellReference(columnList.indexOf(actualInQtyLabel), nextRowNumber)+ ")";
                setCellFormula( row, (short)nextColumnIdx++, formula, floatValueStyle );       
                
                nextColumnIdx++;
                
                // Starting Qty Value
                formula = getCellReference(columnList.indexOf(startingQtyLabel), nextRowNumber)+ 
				"*"+getCellReference(columnList.indexOf(unitStdCostInLabel), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, formula, textValueStyle );       
                
                // In From Assy Value
                formula = getCellReference(columnList.indexOf(inQtyLabel), nextRowNumber)+ 
				"*"+getCellReference(columnList.indexOf(unitStdCostInLabel), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, formula, textValueStyle );      

                // In From RMA Value
                formula = getCellReference(columnList.indexOf(inFromRMALabel), nextRowNumber)+ 
				"*"+getCellReference(columnList.indexOf(unitStdCostInLabel), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, formula, textValueStyle );  
                
                // In From Other Value
                formula = getCellReference(columnList.indexOf(inFromOtherLabel), nextRowNumber)+ 
				"*"+getCellReference(columnList.indexOf(unitStdCostInLabel), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, formula, textValueStyle );  
                
                // Available Qty Value
                formula = getCellReference(columnList.indexOf(availableQtyLabel), nextRowNumber)+ 
				"*"+getCellReference(columnList.indexOf(unitStdCostInLabel), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, formula, textValueStyle );                   

                // Scrap Qty Value
                formula = getCellReference(columnList.indexOf(scrapQtyLabel), nextRowNumber)+ 
				"*"+getCellReference(columnList.indexOf(unitStdCostInLabel), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, formula, textValueStyle );                   

                // Ending Qty Value
                formula = getCellReference(columnList.indexOf(endingQtyLabel), nextRowNumber)+ 
				"*"+getCellReference(columnList.indexOf(unitStdCostInLabel), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, formula, textValueStyle );                   

                // Std Value into Operation
                formula = getCellReference(columnList.indexOf(actualInQtyLabel), nextRowNumber)+ 
				"*"+getCellReference(columnList.indexOf(unitStdCostInLabel), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, formula, floatValueStyle );                   

                // Std Value Add at Test
                formula = getCellReference(columnList.indexOf(actualInQtyLabel), nextRowNumber)+ 
				"*"+getCellReference(columnList.indexOf(stdUnitValueAddLabel), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, formula, floatValueStyle );  

                // Std Value Out Of Operation
                formula = getCellReference(columnList.indexOf(outQtyLabel), nextRowNumber)+ 
				"*"+getCellReference(columnList.indexOf(stdUnitCostOutLabel), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, formula, floatValueStyle );  
                
                // Yield Variance
                formula = getCellReference(columnList.indexOf(stdValueOutOperation), nextRowNumber)+ 
                "-"+getCellReference(columnList.indexOf(stdValueIntoOperation), nextRowNumber)+
				"-"+getCellReference(columnList.indexOf(stdValueAddAtTest), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, formula, floatValueStyle ); 
                
                nextColumnIdx++;
                
                setCellValue( row, (short)nextColumnIdx++, transactionDetail.getUnitStdCostIn(), floatValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, (short)nextColumnIdx++, transactionDetail.getUnitStdValueAdd(), floatValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, (short)nextColumnIdx++, transactionDetail.getUnitStdCostOut(), floatValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, (short)nextColumnIdx++, transactionDetail.getStdYield(), floatValueStyle, Cell.CELL_TYPE_NUMERIC  );
				*/

            }
            else if ( "Tape & Reel".equalsIgnoreCase( detailedTransactionReportForm.getProcessType() ) )
            {
            	setCellValue( row, nextColumnIdx++, transactionDetail.getInQty(), textValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, nextColumnIdx++, transactionDetail.getInFromFGQty(), textValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, nextColumnIdx++, transactionDetail.getInFromRMA(), textValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, nextColumnIdx++, transactionDetail.getInFromOther(), textValueStyle, Cell.CELL_TYPE_NUMERIC  );
                String availabelQtyFormula = getCellReference(columnList.indexOf(startingQtyLabel), nextRowNumber) +
                                        "+" + getCellReference(columnList.indexOf(inQtyLabel), nextRowNumber) +
                                        "+" + getCellReference(columnList.indexOf(inFromFGLabel), nextRowNumber) + 
                                        "+" + getCellReference(columnList.indexOf(inFromRMALabel), nextRowNumber)+
                                        "+" + getCellReference(columnList.indexOf(inFromOtherLabel), nextRowNumber);
                
                setCellFormula( row, (short)nextColumnIdx++, availabelQtyFormula, textValueStyle );
                setCellValue( row, nextColumnIdx++, transactionDetail.getScrapQty(), textValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, nextColumnIdx++, transactionDetail.getEndingQty(), textValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, nextColumnIdx++, transactionDetail.getTransferToBulk(), textValueStyle, Cell.CELL_TYPE_NUMERIC  );
                
                String actualQtyInFormula = getCellReference(columnList.indexOf(availableQtyLabel), nextRowNumber) +
                                    "-" + getCellReference(columnList.indexOf(scrapQtyLabel), nextRowNumber) +
                                    "-" + getCellReference(columnList.indexOf(endingQtyLabel), nextRowNumber)+
                                    "-" + getCellReference(columnList.indexOf( transferToBulk ), nextRowNumber);
                
                setCellFormula( row, (short)nextColumnIdx++, actualQtyInFormula, textValueStyle );
                setCellValue( row, nextColumnIdx++, transactionDetail.getOutQty(), textValueStyle, Cell.CELL_TYPE_NUMERIC  );
                String yieldLossFormula = getCellReference(columnList.indexOf(actualInQtyLabel), nextRowNumber) +
                                    "-" + getCellReference(columnList.indexOf(outQtyLabel), nextRowNumber) ;
                setCellFormula( row, (short)nextColumnIdx++, yieldLossFormula, textValueStyle );
                /*
                nextColumnIdx++;
                //  stdOutUnits
                String formula = getCellReference(columnList.indexOf(actualInQtyLabel), nextRowNumber)+ 
                "*"+getCellReference(columnList.indexOf(stdYieldLabel), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, formula, textValueStyle );
                // Units Yield Variance
                formula = getCellReference(columnList.indexOf(outQtyLabel), nextRowNumber)+ 
				"-"+getCellReference(columnList.indexOf(stdOutUnits), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, formula, textValueStyle );       
                // Actaul Yield
                formula = "IF(" + getCellReference(columnList.indexOf(actualInQtyLabel), nextRowNumber)+ "=0, 0,"
				+getCellReference(columnList.indexOf(outQtyLabel), nextRowNumber)
				+ "/" + getCellReference(columnList.indexOf(actualInQtyLabel), nextRowNumber)+ ")";
                setCellFormula( row, (short)nextColumnIdx++, formula, floatValueStyle );       
                
                nextColumnIdx++;
                
                // Starting Qty Value
                formula = getCellReference(columnList.indexOf(startingQtyLabel), nextRowNumber)+ 
				"*"+getCellReference(columnList.indexOf(unitStdCostInLabel), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, formula, textValueStyle );       
                
                // In From Test Value
                formula = getCellReference(columnList.indexOf(inQtyLabel), nextRowNumber)+ 
				"*"+getCellReference(columnList.indexOf(unitStdCostInLabel), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, formula, textValueStyle );      

                // In From FG Value
                formula = getCellReference(columnList.indexOf(inFromFGLabel), nextRowNumber)+ 
				"*"+getCellReference(columnList.indexOf(unitStdCostInLabel), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, formula, textValueStyle );  
                
                // In From RMA Value
                formula = getCellReference(columnList.indexOf(inFromRMALabel), nextRowNumber)+ 
				"*"+getCellReference(columnList.indexOf(unitStdCostInLabel), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, formula, textValueStyle );  
                
                // In From Other Value
                formula = getCellReference(columnList.indexOf(inFromOtherLabel), nextRowNumber)+ 
				"*"+getCellReference(columnList.indexOf(unitStdCostInLabel), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, formula, textValueStyle );  
                
                // Available Qty Value
                formula = getCellReference(columnList.indexOf(availableQtyLabel), nextRowNumber)+ 
				"*"+getCellReference(columnList.indexOf(unitStdCostInLabel), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, formula, textValueStyle );                   

                // Scrap Qty Value
                formula = getCellReference(columnList.indexOf(scrapQtyLabel), nextRowNumber)+ 
				"*"+getCellReference(columnList.indexOf(unitStdCostInLabel), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, formula, textValueStyle );                   

                // Ending Qty Value
                formula = getCellReference(columnList.indexOf(endingQtyLabel), nextRowNumber)+ 
				"*"+getCellReference(columnList.indexOf(unitStdCostInLabel), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, formula, textValueStyle );                   

                // Std Value into Operation
                formula = getCellReference(columnList.indexOf(actualInQtyLabel), nextRowNumber)+ 
				"*"+getCellReference(columnList.indexOf(unitStdCostInLabel), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, formula, floatValueStyle );                   

                // Std Value Add at Test
                formula = getCellReference(columnList.indexOf(actualInQtyLabel), nextRowNumber)+ 
				"*"+getCellReference(columnList.indexOf(stdUnitValueAddLabel), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, formula, floatValueStyle );  

                // Std Value Out Of Operation
                formula = getCellReference(columnList.indexOf(outQtyLabel), nextRowNumber)+ 
				"*"+getCellReference(columnList.indexOf(stdUnitCostOutLabel), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, formula, floatValueStyle );  
                
                // Yield Variance
                formula = getCellReference(columnList.indexOf(stdValueOutOperation), nextRowNumber)+ 
                "-"+getCellReference(columnList.indexOf(stdValueIntoOperation), nextRowNumber)+
				"-"+getCellReference(columnList.indexOf(stdValueAddAtTR), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, formula, floatValueStyle ); 
                
                nextColumnIdx++;
                
                setCellValue( row, (short)nextColumnIdx++, transactionDetail.getUnitStdCostIn(), floatValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, (short)nextColumnIdx++, transactionDetail.getUnitStdValueAdd(), floatValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, (short)nextColumnIdx++, transactionDetail.getUnitStdCostOut(), floatValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, (short)nextColumnIdx++, transactionDetail.getStdYield(), floatValueStyle, Cell.CELL_TYPE_NUMERIC  );
				*/
            }
            else if ("RMA".equalsIgnoreCase(detailedTransactionReportForm.getProcessType() ) )
            {
            	setCellValue( row, nextColumnIdx++, transactionDetail.getShipmentsToCustomer(), textValueStyle,Cell.CELL_TYPE_NUMERIC );
                setCellValue( row, nextColumnIdx++, transactionDetail.getInFromOther(), textValueStyle, Cell.CELL_TYPE_NUMERIC  );
                //String availabelQtyFormula = getCellReference(columnList.indexOf(startingQtyLabel), nextRowNumber) +
                                       // "+" + getCellReference(columnList.indexOf(inFromOtherLabel), nextRowNumber);
                //setCellFormula( row, (short)nextColumnIdx++, availabelQtyFormula, textValueStyle );
                setCellValue( row, nextColumnIdx++, transactionDetail.getOutQty(), textValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, nextColumnIdx++, transactionDetail.getInFromTRQty(), textValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, nextColumnIdx++, transactionDetail.getInFromFGQty(), textValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, nextColumnIdx++, transactionDetail.getScrapQty(), textValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, nextColumnIdx++, transactionDetail.getEndingQty(), textValueStyle, Cell.CELL_TYPE_NUMERIC  );
                /*
                nextColumnIdx++; // Empty Row
                
                String formula = getCellReference(columnList.indexOf(startingQtyLabel), nextRowNumber) + 
                				"*" + getCellReference(columnList.indexOf(unitStdCostInLabel), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, formula, textValueStyle );
                
                formula = getCellReference(columnList.indexOf(shipmentsToCustomerLabel), nextRowNumber) + 
				"*" + getCellReference(columnList.indexOf(unitStdCostInLabel), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, formula, textValueStyle );
                
                formula = getCellReference(columnList.indexOf(inFromOtherLabel), nextRowNumber) + 
				"*" + getCellReference(columnList.indexOf(unitStdCostInLabel), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, formula, textValueStyle );

                formula = getCellReference(columnList.indexOf(outQtyFTLabel), nextRowNumber) + 
				"*" + getCellReference(columnList.indexOf(unitStdCostInLabel), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, formula, textValueStyle );

                formula = getCellReference(columnList.indexOf(outQtyTRLabel), nextRowNumber) + 
				"*" + getCellReference(columnList.indexOf(unitStdCostInLabel), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, formula, textValueStyle );
                
                formula = getCellReference(columnList.indexOf(outQtyFGLabel), nextRowNumber) + 
				"*" + getCellReference(columnList.indexOf(unitStdCostInLabel), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, formula, textValueStyle );
                
                formula = getCellReference(columnList.indexOf( scrapQtyLabel ), nextRowNumber) + 
				"*" + getCellReference(columnList.indexOf(unitStdCostInLabel), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, formula, textValueStyle );
                
                formula = getCellReference(columnList.indexOf( endingQtyLabel ), nextRowNumber) + 
				"*" + getCellReference(columnList.indexOf(unitStdCostInLabel), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, formula, textValueStyle );

                nextColumnIdx++; // Empty Row
                
                setCellValue( row, (short)nextColumnIdx++, transactionDetail.getUnitStdCostIn(), floatValueStyle, Cell.CELL_TYPE_NUMERIC  );
                */
            }
            else//FG
            {
            	setCellValue( row, nextColumnIdx++, transactionDetail.getInQty(), textValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, nextColumnIdx++, transactionDetail.getInFromTRQty(), textValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, nextColumnIdx++, transactionDetail.getInFromRMA(), textValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, nextColumnIdx++, transactionDetail.getInFromOther(), textValueStyle, Cell.CELL_TYPE_NUMERIC  );
                String availabelQtyFormula = getCellReference(columnList.indexOf(startingQtyLabel), nextRowNumber) +
                                        "+" + getCellReference(columnList.indexOf(inQtyLabel), nextRowNumber) +
                                        "+" + getCellReference(columnList.indexOf(inFromTRLabel), nextRowNumber) + 
                                        "+" + getCellReference(columnList.indexOf(inFromRMALabel), nextRowNumber)+
                                        "+" + getCellReference(columnList.indexOf(inFromOtherLabel), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, availabelQtyFormula, textValueStyle );
                setCellValue( row, nextColumnIdx++, transactionDetail.getOutQty(), textValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, nextColumnIdx++, transactionDetail.getShipmentsToCustomer(), textValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, nextColumnIdx++, transactionDetail.getScrapQty(), textValueStyle, Cell.CELL_TYPE_NUMERIC  );
                setCellValue( row, nextColumnIdx++, transactionDetail.getEndingQty(), textValueStyle, Cell.CELL_TYPE_NUMERIC  );
                String actualQtyInFormula = getCellReference(columnList.indexOf(availableQtyLabel), nextRowNumber) +
                                    "-" + getCellReference(columnList.indexOf(scrapQtyLabel), nextRowNumber) +
                                    "-" + getCellReference(columnList.indexOf(endingQtyLabel), nextRowNumber) ;
                setCellFormula( row, (short)nextColumnIdx++, actualQtyInFormula, textValueStyle );
                String actualQtyOutFormula = getCellReference(columnList.indexOf(outQtyLabel), nextRowNumber) +
                                    "+" + getCellReference(columnList.indexOf(shipmentsToCustomerLabel), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, actualQtyOutFormula, textValueStyle );

                String yieldLossFormula = getCellReference(columnList.indexOf(actualInQtyLabel), nextRowNumber) +
                                    "-" + getCellReference(columnList.indexOf(actualOutQtyLabel), nextRowNumber) ;
                setCellFormula( row, (short)nextColumnIdx++, yieldLossFormula, textValueStyle );
                /*
                nextColumnIdx++; // Empty Row
                
                String formula = getCellReference(columnList.indexOf(startingQtyLabel), nextRowNumber) + 
                				"*" + getCellReference(columnList.indexOf(unitStdCostInLabel), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, formula, textValueStyle );
                
                formula = getCellReference(columnList.indexOf(inQtyLabel), nextRowNumber) + 
				"*" + getCellReference(columnList.indexOf(unitStdCostInLabel), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, formula, textValueStyle );
                
                formula = getCellReference(columnList.indexOf(inFromTRLabel), nextRowNumber) + 
				"*" + getCellReference(columnList.indexOf(unitStdCostInLabel), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, formula, textValueStyle );

                formula = getCellReference(columnList.indexOf(inFromRMALabel), nextRowNumber) + 
				"*" + getCellReference(columnList.indexOf(unitStdCostInLabel), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, formula, textValueStyle );

                formula = getCellReference(columnList.indexOf(inFromOtherLabel), nextRowNumber) + 
				"*" + getCellReference(columnList.indexOf(unitStdCostInLabel), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, formula, textValueStyle );
                
                formula = getCellReference(columnList.indexOf(availableQtyLabel), nextRowNumber) + 
				"*" + getCellReference(columnList.indexOf(unitStdCostInLabel), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, formula, textValueStyle );
                
                formula = getCellReference(columnList.indexOf(outQtyLabel), nextRowNumber) + 
				"*" + getCellReference(columnList.indexOf(unitStdCostInLabel), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, formula, textValueStyle );
                
                formula = getCellReference(columnList.indexOf(shipmentsToCustomerLabel), nextRowNumber) + 
				"*" + getCellReference(columnList.indexOf(unitStdCostInLabel), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, formula, textValueStyle );
                
                formula = getCellReference(columnList.indexOf(scrapQtyLabel), nextRowNumber) + 
				"*" + getCellReference(columnList.indexOf(unitStdCostInLabel), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, formula, textValueStyle );
                
                formula = getCellReference(columnList.indexOf(endingQtyLabel), nextRowNumber) + 
				"*" + getCellReference(columnList.indexOf(unitStdCostInLabel), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, formula, textValueStyle );
                
                formula = getCellReference(columnList.indexOf(yieldLossLabel), nextRowNumber) + 
				"*" + getCellReference(columnList.indexOf(unitStdCostInLabel), nextRowNumber);
                setCellFormula( row, (short)nextColumnIdx++, formula, textValueStyle );
                
                nextColumnIdx++; // Empty Row
                
                setCellValue( row, (short)nextColumnIdx++, transactionDetail.getUnitStdCostIn(), floatValueStyle, Cell.CELL_TYPE_NUMERIC  );
                */
            }

            nextColumnIdx = 0;
        }
        //--TOTAL ROW
        if ( nextRowNumber > 4 ) //data available
        {
            row = sheet.createRow( nextRowNumber );
            cell = mergeCells ( sheet, nextRowNumber, (short)0, nextRowNumber, (short)0, titleStyle );
            setCellValue ( cell, ch.createRichTextString("Total"), headerStyle );
            //nextColumnIdx = headers.length;
            for ( nextColumnIdx=columnList.indexOf(startingQtyLabel); nextColumnIdx < headers.length; nextColumnIdx++ )
            {
                String totalFormula = "SUM(" + getCellReference(nextColumnIdx, 5) + ":" + getCellReference(nextColumnIdx, nextRowNumber) + ")";
                setCellFormula( row, (short)nextColumnIdx, totalFormula, headerValueStyle );

            }
        }

        return workbook;
    }
    /**
     * merges cells and sets each cell with the given cellStyle.
     * @param sheet
     * sheet object in which the cells has to be merged.
     * @param startRow
     * start row Number to merge from.<br>
     * starts from 0.
     * @param startCell
     * start cell number in the start row to merge from.<br>
     * starts from 0.
     * @param endRow
     * end row Number<br>
     * starts from 0.
     * @param endCell
     * end cell number<br>
     * starts from 0.
     * @param endCell
     * @return
     * returns cell object of the startRow, startCell.
     */
    private Cell mergeCells ( Sheet sheet, int startRow, short startCell, int endRow, short endCell, CellStyle cellStyle )
    {
        sheet.addMergedRegion( new CellRangeAddress( startRow, endRow, startCell, endCell ) );
        Cell cell = null;
        for ( int rowIdx = startRow; rowIdx <= endRow; rowIdx++ )
        {
            for ( int cellIdx = startCell; cellIdx <= endCell; cellIdx++ )
            {
                cell = getCellAt ( sheet.getRow ( rowIdx ), (short)cellIdx );
                cell.setCellStyle ( cellStyle );
            }
        }
        return getCellAt ( sheet.getRow( startRow), startCell );
    }
    /**
     * Returns the cell object at the specified cell in the row.<br>
     * It creats cell if doesn't exists.
     * @param row
     * row object from which cell object has to be picked
     * @param cellNumber
     * starts from 0.
     * @return
     */
    private Cell getCellAt ( Row row, int cellNumber )
    {
        Cell cell = null;
        cell = row.getCell( cellNumber );
        if ( cell == null )
        {
            cell = row.createCell ( cellNumber );
        }
        return cell;
    }
    private void setCellValue ( Cell cell, RichTextString value, CellStyle cellStyle )
    {
        if ( cellStyle != null )
        {
            cell.setCellStyle( cellStyle);
        }
        cell.setCellValue( value );
    }
    
    private void setCellValue ( Row row, int cellNo, RichTextString value, CellStyle cellStyle )
    {
        Cell cell   = row.createCell( cellNo );
        if ( cellStyle != null )
        {
            cell.setCellStyle( cellStyle);
        }
        cell.setCellValue( value );
    }
     private void setCellValue ( Row row, int cellNo, int value, CellStyle cellStyle, int cellType )
    {
        Cell cell   = row.createCell( cellNo );
        if ( cellStyle != null )
        {
            cell.setCellStyle( cellStyle);
        }
        cell.setCellValue( value );
        cell.setCellType( cellType );
    }
    
     /*private void setCellValue ( Row row, int cellNo, double value, CellStyle cellStyle, int cellType )
     {
         Cell cell   = row.createCell( cellNo );
         if ( cellStyle != null )
         {
             cell.setCellStyle( cellStyle);
         }
         cell.setCellValue( value );
         cell.setCellType( cellType );
     }*/
     
    private static void setCellFormula ( Row row, int cellNo, String formula, CellStyle cellStyle )
    {
        Cell cell   = row.createCell( cellNo );
        if ( cellStyle != null )
        {
            cell.setCellStyle( cellStyle);
        }
        cell.setCellFormula( formula );
    }
    /**
     *
     * @param idx - zero based index
     *
     * @return
     */
    private static String getAlphaforIdx ( int idx )
    {
        String[] alpha = {"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"};
        String formatted = "";
        if ( idx < alpha.length )
        {
            formatted = alpha[idx];
        }
        else
        {
            //idx=idx+1;
            int idx1 = (idx/26)-1;
            int idx2 = (idx%26);
            if ( idx1 >= 0 )
            {
                formatted = alpha[idx1];
            }
            if ( idx2 >= 0 )
            {
                formatted = formatted+alpha[idx2];
            }
        }
        return formatted;
    }

    /**
     *
     * @param columnIdx  zero based
     * @param rowNumber starts from one
     * @return
     */
    private String getCellReference ( int columnIdx, int rowNumber )
    {
        return getAlphaforIdx( columnIdx ) + rowNumber;
    }
}
